#region
/* Adoor.NET - Object/Relational mapping framework leveraging ADO.NET`s powerful disconnected mode.
 * 
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *  
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *  
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */ 
#endregion

using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Text;
using System.Diagnostics;
using System.Data;
using System.Data.Common;
using System.Data.SqlTypes;
using Adoor.Relational.SqlDriver;

using System.ComponentModel;
using System.ComponentModel.Design;
using System.Drawing;
using System.Drawing.Design;  

namespace Adoor.Relational
{
	public interface IDbRelationalSource: IRelationalSource
	{
        void UpdateTable(DataTable table, string updateCommand, string insertCommand, string deleteCommand, ICollection<DataColumn> dataColumns);
        event QueryTraceEventHandler QueryTrace;
        void UpdateTableInsert(DataTable table, string insertCommand, ICollection<DataColumn> dataColumns);
        void UpdateTableUpdate(DataTable table, string updateCommand, ICollection<DataColumn> dataColumns);
        void UpdateTableDelete(DataTable table, string deleteCommand, ICollection<DataColumn> dataColumns);
	}

   

	// common to OleDb & SqlClient
    [System.ComponentModel.ToolboxItem(false),System.ComponentModel.DesignTimeVisible(false)]     
    public abstract class CommonDbRelationalSource : System.ComponentModel.Component, IDbRelationalSource, Adoor.Data.IDbConnectionSupplier  
	{
        //protected static log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);


        protected CommonDbRelationalSource(
            System.Data.Common.DbProviderFactory dbProviderFactory
            , ISqlDriver sqlDriver
            )
        {
            this.sqlDriver = sqlDriver;
            this.dbProviderFactory = dbProviderFactory;
            this.refreshDataFromDatabase= true;
           
        }

		protected CommonDbRelationalSource(
            System.Data.Common.DbProviderFactory dbProviderFactory
            , ISqlDriver sqlDriver
            , System.Data.Common.DbConnectionStringBuilder connectionStringBuilder)
		{
			this.sqlDriver = sqlDriver;
            this.dbProviderFactory = dbProviderFactory;
            this.connectionStringBuilder = connectionStringBuilder;
		}

        private System.Data.Common.DbConnectionStringBuilder connectionStringBuilder;
        protected System.Data.Common.DbConnectionStringBuilder ConnectionStringBuilder
        { get { return connectionStringBuilder; } }

        private Adoor.Relational.DbconnectionStringProvider m_connectionStringBuilder;

        [DefaultValue((string)null), Editor("Adoor.VSDesigner.DbconnectionStringProviderEditor, Adoor.VSDesigner", typeof(UITypeEditor))]
        public Adoor.Relational.DbconnectionStringProvider connectionStringProvider
        {
            get { return m_connectionStringBuilder; }
            set { m_connectionStringBuilder = value; connectionStringBuilder = value.m_ConnectionStringBuilder; }

        }

		public event QueryEventHandler OnQuery;

		public static DbType Type2DbType(Type type)
		{
			switch(Type.GetTypeCode(type))
			{
				case TypeCode.Boolean:
					return DbType.Boolean;
				case TypeCode.Byte:
					return DbType.Byte;
				case TypeCode.Char:
					return DbType.String;
				case TypeCode.DateTime:
					return DbType.DateTime;
				case TypeCode.Decimal:
					return DbType.Decimal;
				case TypeCode.Double:
					return DbType.Double;
				case TypeCode.Int16:
					return DbType.Int16;
				case TypeCode.Int32:
					return DbType.Int32;
				case TypeCode.Int64:
					return DbType.Int64;
				case TypeCode.SByte:
					return DbType.SByte;
				case TypeCode.Single:
					return DbType.Single;
				case TypeCode.String:
					return DbType.String;
				case TypeCode.UInt16:
					return DbType.UInt16;
				case TypeCode.UInt32:
					return DbType.UInt32;
				case TypeCode.UInt64:
					return DbType.UInt64;				
			}
			if (type == typeof(byte[]))
				return DbType.Binary;
			if (type == typeof(Guid))
				return DbType.Guid;

			if (type == typeof(SqlBoolean))
				return DbType.Boolean;
			if (type == typeof(SqlByte))
				return DbType.Byte;
			if (type == typeof(SqlDateTime))
				return DbType.DateTime;
			if (type == typeof(SqlDecimal))
				return DbType.Decimal;
			if (type == typeof(SqlDouble))
				return DbType.Double;
			if (type == typeof(SqlInt16))
				return DbType.Int16;
			if (type == typeof(SqlInt32))
				return DbType.Int32;
			if (type == typeof(SqlInt64))
				return DbType.Int64;
			if (type == typeof(SqlSingle))
				return DbType.Single;
			if (type == typeof(SqlString))
				return DbType.String;
			if (type == typeof(SqlGuid))
				return DbType.Guid;
			if (type == typeof(DBNull))
				return DbType.String;

			throw new Exception("type " + type.Name + " not handled");
		}

		public string DefaultSelectIsolationLevelQuery
		{
			get
			{
				return sqlDriver.DefaultSelectIsolationLevelQuery;
			}
		}

        protected abstract void EscapeCommandText(ref string commandText, IDataParameterCollection parameters);

		public virtual void ExecuteTable(DataTable dt, string commandText, IDictionary<string, object> args)
		{
			using(IDbCommand command = dbProviderFactory.CreateCommand())
			{
                using (IDbConnection connection = dbProviderFactory.CreateConnection())
				{
                    connection.ConnectionString = connectionStringBuilder.ConnectionString;
                    command.Connection = connection;
					command.CommandTimeout = 0;
					string[] newArgIndexes = new string[args.Count];				

					int idxArg = 0;

					IEnumerator<KeyValuePair<string, object>> dicEnum = args.GetEnumerator();
					while(dicEnum.MoveNext())
					{
						string paramName = dicEnum.Current.Key;
						object o = dicEnum.Current.Value;

						Array argArray = o as Array;
						if (argArray != null) 
						{						
							StringBuilder sbParams = new StringBuilder();
							int nb = 0;
							bool needComma =false;
							foreach(object itemVal in argArray)
							{
								if(needComma)
									sbParams.Append(", ");

								string subParamName = paramName + (++nb).ToString();
								sbParams.Append(subParamName);
							
								IDbDataParameter p = command.CreateParameter();
								p.ParameterName = subParamName;
							
								object val = itemVal;
								Type valType = val.GetType();
								if(valType.IsEnum)
									p.Value = (int) val;
								else
									p.Value = val;
								p.DbType = CommonDbRelationalSource.Type2DbType(p.Value.GetType());

								command.Parameters.Add(p);
								needComma = true;
							}
							commandText = commandText.Replace(paramName, sbParams.ToString());
						}
						else
						{
							IDbDataParameter p = command.CreateParameter();
							//#if SqlClient
							p.ParameterName = paramName;
							//#endif
					
							Type valType = o.GetType();
							if(valType.IsEnum)
								p.Value = (int) o;
							else
								p.Value = o;

							p.Value = o;
							p.DbType = CommonDbRelationalSource.Type2DbType(p.Value.GetType());
							command.Parameters.Add(p);
						}

						idxArg++;
					}				

					EscapeCommandText(ref commandText, command.Parameters);

                    AdoorLoger.Debug(Tracer.GetTrace(commandText, command));

					OnQueryTrace(commandText, command);

					command.CommandText = commandText;
                    DbDataAdapter oleDbDataAdapter = dbProviderFactory.CreateDataAdapter();
                    oleDbDataAdapter.SelectCommand = (DbCommand) command;
					connection.Open();
					if(DefaultSelectIsolationLevelQuery != string.Empty)
					{
						IDbCommand cmd = connection.CreateCommand();
						cmd.CommandText = DefaultSelectIsolationLevelQuery;
						cmd.ExecuteNonQuery();
					}
					
					bool retry = false;
					do
					{
						try
						{
							oleDbDataAdapter.Fill(dt);
						}
						catch(Exception ex)
						{
							bool handled;
							retry = OnRemoteSourceRetry(this, retry, ex, out handled);
							if(!retry)
							{
								if(handled)
									throw new Adoor.CancelException(ex.Message, ex);
								else
									throw ex;
							}
						}
					}while(retry);
					
				}
			}
		}

		public void CancelCurrentQuery()
		{
			
		}

		private Adoor.Relational.RelationalSource.RelationalSourceTrace tracer;

		protected Adoor.Relational.RelationalSource.RelationalSourceTrace Tracer
		{
			get
			{ 
				if(tracer == null)
					tracer = CreateTracer();
				return tracer;
			}
		}

		protected virtual Adoor.Relational.RelationalSource.RelationalSourceTrace CreateTracer()
		{
			return new Adoor.Relational.RelationalSource.RelationalSourceTrace();
		}

		/// <summary>
		/// Fill DataTable "dt" with the result of "commandText"
		/// </summary>
		/// <param name="commandText"></param>
		/// <param name="dt"></param>
        public virtual void ExecuteTable(DataTable dt, string commandText, System.Collections.ICollection args)
		{
			System.Collections.IEnumerator it = args.GetEnumerator();
			if(args.Count == 1 && it.MoveNext() && it.Current is IDictionary<string, object>)
			{
				ExecuteTable(dt, commandText, it.Current as IDictionary<string, object>);
				return;
			}
			
			using(IDbCommand command = dbProviderFactory.CreateCommand())
			{
				using(IDbConnection connection = dbProviderFactory.CreateConnection())
				{
                    connection.ConnectionString = ConnectionStringBuilder.ConnectionString;
                    command.Connection = connection;
					command.CommandTimeout = 0;
					int i = 0;
					string[] newArgIndexes = new string[args.Count];				

					int idxArg = 0;
					foreach(object o in args) 
					{
						Array argArray = o as Array;
						if (argArray != null) 
						{
							StringBuilder sbParams = new StringBuilder();
							int max = i + argArray.Length;
							bool needComma =false;
							for(int idx = i; idx < max; idx++)
							{
								if(needComma)
									sbParams.Append(", ");

								sbParams.Append("{");
								sbParams.Append(idx);
								sbParams.Append("}");

								IDbDataParameter p = command.CreateParameter();
								p.ParameterName = "@p" + (idx + 1).ToString();
							
								object val = argArray.GetValue(argArray.Length - (max - idx));
								Type valType = val.GetType();
								if(valType.IsEnum)
									p.Value = (int) val;
								else
									p.Value = val;
								p.DbType = CommonDbRelationalSource.Type2DbType(p.Value.GetType());

								command.Parameters.Add(p);
								i++;
								needComma = true;
							}
							newArgIndexes[idxArg] = sbParams.ToString();

						}
						else
						{
							newArgIndexes[idxArg] = string.Concat("{", i, "}");
							IDbDataParameter p = command.CreateParameter();
							//#if SqlClient
							p.ParameterName = "@p" + (++i).ToString();
							//#endif
					
							Type valType = o.GetType();
							if(valType.IsEnum)
								p.Value = (int) o;
							else
								p.Value = o;

							p.Value = o;
							p.DbType = CommonDbRelationalSource.Type2DbType(p.Value.GetType());
							command.Parameters.Add(p);
						}

						idxArg++;
					}
					commandText = string.Format(commandText, newArgIndexes);

					EscapeCommandText(ref commandText, command.Parameters);

                    AdoorLoger.Debug(Tracer.GetTrace(commandText, command));

					OnQueryTrace(commandText, command);
					command.CommandText = commandText;
					command.CommandTimeout = 0;
                    DbDataAdapter oleDbDataAdapter = dbProviderFactory.CreateDataAdapter();
                    oleDbDataAdapter.SelectCommand = (DbCommand) command;
					connection.Open();
					if(DefaultSelectIsolationLevelQuery != string.Empty)
					{
						IDbCommand cmd = connection.CreateCommand();
						cmd.CommandText = DefaultSelectIsolationLevelQuery;
						cmd.ExecuteNonQuery();
					}

					bool retry = false;
					do
					{
						try
						{
							oleDbDataAdapter.Fill(dt);
						}
						catch(Exception ex)
						{
							bool handled;
							retry = OnRemoteSourceRetry(this, retry, ex, out handled);
							if(!retry)
							{
								if(handled)
									throw new Adoor.CancelException(ex.Message, ex);
								else
									throw ex;
							}
						}
					}while(retry);
					
				}
			}
		}

		public event CommandEventHandler CommandCreated;

		protected virtual void OnCommandCreated(IDbCommand cmd)
		{
			if(CommandCreated != null)
				CommandCreated(this, new CommandEventArgs(cmd));
		}

		protected virtual void OnQueryTrace(string commandeText, IDbCommand cmd)
		{
			if(QueryTrace != null)
			{
				string trace = Tracer.GetTrace(commandeText, cmd);
				QueryTraceEventArgs e = new QueryTraceEventArgs(trace);
				QueryTrace(this, e);
			}
		}

		public virtual IDataReader ExecuteReader(RelationalQuery query, System.Collections.ICollection args)
		{
			if (this.OnQuery != null) 
			{
				QueryEventArgs qeargs = new QueryEventArgs(query, ExpressionKind.Aggregate);
				this.OnQuery(this, qeargs);
				query = qeargs.Query;
			}

			string c;
			lock(sqlDriver)
			{
				c = sqlDriver.SelectCommand(query)[0];
			}

			IDbCommand selectCommand = dbProviderFactory.CreateCommand();
			try
			{
                IDbConnection connection = dbProviderFactory.CreateConnection();
                connection.ConnectionString = ConnectionStringBuilder.ConnectionString;
                selectCommand.Connection = connection;
				try
				{
					selectCommand.CommandTimeout = 0;
					int i = 0;
					foreach(object o in args) 
					{
						IDbDataParameter p = selectCommand.CreateParameter();
						//#if SqlClient
						p.ParameterName = "@p" + (++i).ToString();
						//#endif
						p.Value = o;
						selectCommand.Parameters.Add(p);
					}
					EscapeCommandText(ref c, selectCommand.Parameters);
					selectCommand.CommandText = c;
					bool retry = false;
					do
					{
						try
						{
							connection.Open();
							if(DefaultSelectIsolationLevelQuery != string.Empty)
							{
								IDbCommand cmd = connection.CreateCommand();
								cmd.CommandText = DefaultSelectIsolationLevelQuery;
								cmd.ExecuteNonQuery();
							}
							return selectCommand.ExecuteReader(CommandBehavior.CloseConnection);  // IDataReader.Close will close the connection
						}
						catch(Exception ex)
						{
							bool handled;
							retry = OnRemoteSourceRetry(this, retry, ex, out handled);
							if(!retry)
							{
								if(handled)
									throw new Adoor.CancelException(ex.Message, ex);
								else
									throw ex;
							}
						}
					}while(retry);
					throw new Exception();
				}
				catch(Exception)
				{
					connection.Dispose();
					throw;
				}
			}
			catch(Exception)
			{
				selectCommand.Dispose();
				throw;
			}
		}

		public void ExecuteDelete(RelationalOperator op, System.Collections.ICollection args)
		{
			RelationalQuery query = op.AsQuery();
			if (this.OnQuery != null)
			{
				QueryEventArgs qeargs = new QueryEventArgs(query, ExpressionKind.Aggregate);
				this.OnQuery(this, qeargs);
				query = qeargs.Query;
			}

			string c;
			lock(sqlDriver)
			{
				c = sqlDriver.DeleteCommand(query);
			}

			DataTable dt = Adoor.Data.DataUtils.CreateCaseInsesitiveDataTable();
			ExecuteTable(dt, c, args);
		}

		public void ExecuteNonQuery(RelationalQuery query, System.Collections.ICollection args)
		{
			if (this.OnQuery != null)
			{
				QueryEventArgs qeargs = new QueryEventArgs(query, ExpressionKind.Aggregate);
				this.OnQuery(this, qeargs);
				query = qeargs.Query;
			}

			
			string[] queries;
			
			lock(sqlDriver)
			{
				queries = sqlDriver.NonQueryCommand(query);
			}

			foreach( string c in queries)
			{
				ExecuteNonQuery(c, args);
			}			
		}

		public void ExecuteNonQuery(RelationalQuery query, IDictionary<string, object> args)
		{
			if (this.OnQuery != null)
			{
				QueryEventArgs qeargs = new QueryEventArgs(query, ExpressionKind.Aggregate);
				this.OnQuery(this, qeargs);
				query = qeargs.Query;
			}

			string[] queries;
			
			lock(sqlDriver)
			{
				queries = sqlDriver.NonQueryCommand(query);
			}

			foreach( string c in queries)
			{
				ExecuteNonQuery(c, args);
			}			
		}

		public virtual void ExecuteNonQuery(string commandText, System.Collections.ICollection args)
		{
			System.Collections.IEnumerator it = args.GetEnumerator();
			if(args.Count == 1 && it.MoveNext() && it.Current is IDictionary<string, object>)
			{
				ExecuteNonQuery(commandText, it.Current as IDictionary<string, object>);
				return;
			}
			
			using(IDbCommand command = dbProviderFactory.CreateCommand())
			{
                using (IDbConnection connection = dbProviderFactory.CreateConnection()) 
				{
                    connection.ConnectionString = ConnectionStringBuilder.ConnectionString;
                    command.Connection = connection;
					
					command.CommandTimeout = 0;
					int i = 0;
					string[] newArgIndexes = new string[args.Count];				

					int idxArg = 0;
					foreach(object o in args) 
					{
						Array argArray = o as Array;
						if (argArray != null) 
						{
							StringBuilder sbParams = new StringBuilder();
							int max = i + argArray.Length;
							bool needComma =false;
							for(int idx = i; idx < max; idx++)
							{
								if(needComma)
									sbParams.Append(", ");

								sbParams.Append("{");
								sbParams.Append(idx);
								sbParams.Append("}");

								IDbDataParameter p = command.CreateParameter();
								p.ParameterName = "@p" + (idx + 1).ToString();
							
								object val = argArray.GetValue(argArray.Length - (max - idx));
								Type valType = val.GetType();
								if(valType.IsEnum)
									p.Value = (int) val;
								else
									p.Value = val;
								p.DbType = CommonDbRelationalSource.Type2DbType(p.Value.GetType());

								command.Parameters.Add(p);
								i++;
								needComma = true;
							}
							newArgIndexes[idxArg] = sbParams.ToString();

						}
						else
						{
							newArgIndexes[idxArg] = string.Concat("{", i, "}");
							IDbDataParameter p = command.CreateParameter();
							//#if SqlClient
							p.ParameterName = "@p" + (++i).ToString();
							//#endif
					
							Type valType = o.GetType();
							if(valType.IsEnum)
								p.Value = (int) o;
							else
								p.Value = o;

							p.Value = o;
							p.DbType = CommonDbRelationalSource.Type2DbType(p.Value.GetType());
							command.Parameters.Add(p);
						}

						idxArg++;
					}
					commandText = string.Format(commandText, newArgIndexes);

					EscapeCommandText(ref commandText, command.Parameters);

					AdoorLoger.Debug(Tracer.GetTrace(commandText, command));

					OnQueryTrace(commandText, command);
					command.CommandText = commandText;
					command.CommandTimeout = 0;
					DbDataAdapter oleDbDataAdapter = dbProviderFactory.CreateDataAdapter();
                    oleDbDataAdapter.SelectCommand = (DbCommand) command;
					connection.Open();
					if(DefaultSelectIsolationLevelQuery != string.Empty)
					{
						IDbCommand cmd = connection.CreateCommand();
						cmd.CommandText = DefaultSelectIsolationLevelQuery;
						cmd.ExecuteNonQuery();
					}

					bool retry = false;
					do
					{
						try
						{
							command.ExecuteNonQuery();
						}
						catch(Exception ex)
						{
							bool handled;
							retry = OnRemoteSourceRetry(this, retry, ex, out handled);
							if(!retry)
							{
								if(handled)
									throw new Adoor.CancelException(ex.Message, ex);
								else
									throw ex;
							}
						}
					}while(retry);
				}
			}
		}

		public virtual void ExecuteNonQuery(string commandText, IDictionary<string, object> args)
		{		
			using(IDbCommand command = dbProviderFactory.CreateCommand())
			{
                using (IDbConnection connection = dbProviderFactory.CreateConnection())
                {
                    connection.ConnectionString = ConnectionStringBuilder.ConnectionString;
                    command.Connection = connection;
					command.CommandTimeout = 0;
					string[] newArgIndexes = new string[args.Count];				

					int idxArg = 0;

					IEnumerator<KeyValuePair<string, object>> dicEnum = args.GetEnumerator();
					while(dicEnum.MoveNext())
					{
						string paramName = dicEnum.Current.Key;
                        object o = dicEnum.Current.Value;

						Array argArray = o as Array;
						if (argArray != null) 
						{						
							StringBuilder sbParams = new StringBuilder();
							int nb = 0;
							bool needComma =false;
							foreach(object itemVal in argArray)
							{
								if(needComma)
									sbParams.Append(", ");

								string subParamName = paramName + (++nb).ToString();
								sbParams.Append(subParamName);
							
								IDbDataParameter p = command.CreateParameter();
								p.ParameterName = subParamName;
							
								object val = itemVal;
								Type valType = val.GetType();
								if(valType.IsEnum)
									p.Value = (int) val;
								else
									p.Value = val;
								p.DbType = CommonDbRelationalSource.Type2DbType(p.Value.GetType());

								command.Parameters.Add(p);
								needComma = true;
							}
							commandText = commandText.Replace(paramName, sbParams.ToString());
						}
						else
						{
							IDbDataParameter p = command.CreateParameter();
							//#if SqlClient
							p.ParameterName = paramName;
							//#endif
					
							Type valType = o.GetType();
							if(valType.IsEnum)
								p.Value = (int) o;
							else
								p.Value = o;

							p.Value = o;
							p.DbType = CommonDbRelationalSource.Type2DbType(p.Value.GetType());
							command.Parameters.Add(p);
						}

						idxArg++;
					}				

					EscapeCommandText(ref commandText, command.Parameters);

					AdoorLoger.Debug(Tracer.GetTrace(commandText, command));
					

					OnQueryTrace(commandText, command);

					command.CommandText = commandText;
					connection.Open();
					if(DefaultSelectIsolationLevelQuery != string.Empty)
					{
						IDbCommand cmd = connection.CreateCommand();
						cmd.CommandText = DefaultSelectIsolationLevelQuery;
						cmd.ExecuteNonQuery();
					}

					bool retry = false;
					do
					{
						try
						{
							command.ExecuteNonQuery();
						}
						catch(Exception ex)
						{
							bool handled;
							retry = OnRemoteSourceRetry(this, retry, ex, out handled);
							if(!retry)
							{
								if(handled)
									throw new Adoor.CancelException(ex.Message, ex);
								else
									throw ex;
							}
						}
					}while(retry);
				}
			}
		}
		

		public void Fill(RelationalQuery query, System.Collections.ICollection args, DataSet dataset)
		{			
			DataTable table = Adoor.Data.DataUtils.CreateCaseInsesitiveDataTable();
			dataset.Tables.Add(table);
			Fill(query, args, table);
		}

		public void Fill(RelationalQuery query, IDictionary<string, object> args, DataSet dataset)
		{
			DataTable table = Adoor.Data.DataUtils.CreateCaseInsesitiveDataTable();
			dataset.Tables.Add(table);
			Fill(query, args, table);
		}

		public void Fill(RelationalQuery query, System.Collections.ICollection args, DataSet dataset, string tableName)
		{
			DataTable table = dataset.Tables[tableName];
			if (table == null)
			{
				table = new DataTable(tableName);
				dataset.Tables.Add(table);
			}
			dataset.Tables.Add(table);
			Fill(query, args, table);
		}

		public void Fill(RelationalQuery query, System.Collections.ICollection args, DataTable table)
		{
			if (this.OnQuery != null)
			{
				QueryEventArgs qeargs = new QueryEventArgs(query, ExpressionKind.Aggregate);
				this.OnQuery(this, qeargs);
				query = qeargs.Query;
			}

			
			if(query is UpdateQuery)
			{
				string[] queries;
				lock(sqlDriver)
				{
					queries = sqlDriver.NonQueryCommand(query);
				}

				foreach( string c in queries)
				{
					ExecuteNonQuery(c, args);
				}
			}
			else
			{
				string c;
				lock(sqlDriver)
				{
					c = sqlDriver.SelectCommand(query)[0];
				}

				ExecuteTable(table, c, args);
			}
		}

		public void Fill(RelationalQuery query, object args, DataTable datatable)
		{
			if(args is IDictionary<string, object>)
				Fill(query, (IDictionary<string, object>) args, datatable);
			else if(args is System.Collections.ICollection)
                Fill(query, (System.Collections.ICollection)args, datatable);
			else
				throw new NotSupportedException();
		}

		public void Fill(RelationalQuery query, IDictionary<string, object> args, DataTable table)
		{
			if (this.OnQuery != null)
			{
				QueryEventArgs qeargs = new QueryEventArgs(query, ExpressionKind.Aggregate);
				this.OnQuery(this, qeargs);
				query = qeargs.Query;
			}

			if(query is UpdateQuery)
			{
				string[] queries;
				lock(sqlDriver)
				{
					queries = sqlDriver.NonQueryCommand(query);
				}

				foreach( string c in queries)
				{
					ExecuteNonQuery(c, args);
				}
			}
			else
			{
				string c;
				lock(sqlDriver)
				{
					c = sqlDriver.SelectCommand(query)[0];
				}

				ExecuteTable(table, c, args);
			}
		}

        public virtual object GetScalar(RelationalQuery query, System.Collections.ICollection args)
		{
			DataTable tempTable = Adoor.Data.DataUtils.CreateCaseInsesitiveDataTable();
			Fill(query, args, tempTable);
			if (tempTable.Rows.Count == 0)
				return null;
			return tempTable.Rows[0][0];
		}

        public Int64 ExecuteCount(RelationalQuery query, System.Collections.ICollection args)
		{
			if (this.OnQuery != null)
			{
				QueryEventArgs qeargs = new QueryEventArgs(query, ExpressionKind.Aggregate);
				this.OnQuery(this, qeargs);
				query = qeargs.Query;
			}

			string c;
			lock(sqlDriver)
			{
				c = sqlDriver.CountCommand(query);
			}

			DataTable dt = Adoor.Data.DataUtils.CreateCaseInsesitiveDataTable();
			ExecuteTable(dt, c, args);
			return Convert.ToInt64(dt.Rows[0][0]);
		}

		//TODO:
		//adCriteriaKey. Use only the primary key. This means if the row exists, change or delete it. 
		//adCriteriaAllCols. Construct the SQL code comparing all columns in the Recordset with their server-side counterparts. This is the approach the CommandBuilder takes. 
		//adCriteriaUpdCols. (Default) Construct the SQL code using only the columns in the recordset that have been modified (touched). 
		//adCriteriaTimeStamp. Construct the SQL code using the timestamp column (if available). 
		protected bool updateWhereAll = false;

		private static LockMode deleteRowLockMode = LockMode.Default;
		public static LockMode DeleteRowLockMode
		{
			get{ return deleteRowLockMode; }
			set{ deleteRowLockMode = value; }
		}

		private static LockMode updateRowLockMode = LockMode.Default;
		public static LockMode UpdateRowLockMode
		{
			get{ return updateRowLockMode; }
			set{ updateRowLockMode = value; }
		}

		private static LockMode insertRowLockMode = LockMode.Default;
		public static LockMode InsertRowLockMode
		{
			get{ return insertRowLockMode; }
			set{ insertRowLockMode = value; }
		}

		//TODO: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/CommandBuilder.asp
		//adCriteriaKey. Use only the primary key. This means if the row exists, change or delete it. 
		//adCriteriaAllCols. Construct the SQL code comparing all columns in the Recordset with their server-side counterparts. This is the approach the CommandBuilder takes. 
		//adCriteriaUpdCols. (Default) Construct the SQL code using only the columns in the recordset that have been modified (touched). 
		//adCriteriaTimeStamp. Construct the SQL code using the timestamp column (if available). 
        public abstract DbDataAdapter GetDataAdapter(DataTable table,
                string selectCommand, string updateCommand
            , string insertCommand, string deleteCommand,
                ICollection<DataColumn> dataColumns);
        

        protected DbProviderFactory dbProviderFactory;
		protected ISqlDriver sqlDriver;
        protected bool refreshDataFromDatabase;

		public event DbRelationSourceRetryEventHandler DbRelationSourceRetry;

		private bool OnRemoteSourceRetry(object sender, bool retry, Exception ex, out bool handled)
		{			
			handled = false;
			DbRelationSourceRetryEventArgs e = new DbRelationSourceRetryEventArgs(retry, ex);
			if(DbRelationSourceRetry != null)
			{
				foreach(Delegate dlg in DbRelationSourceRetry.GetInvocationList())
				{
					dlg.DynamicInvoke(new object[] {sender, e});
					if(e.Handled)
					{
						handled = true;
						break;
					}
				}
			}
			return e.Retry;
		}		

		public event QueryTraceEventHandler QueryTrace;

        #region IDbRelationalSource Members

       
        #endregion

        
        #region IDbRelationalSource Members

        public void UpdateTableInsert(DataTable table, string insertCommand, ICollection<DataColumn> dataColumns)
        {
            if (string.IsNullOrEmpty(insertCommand))
                insertCommand = sqlDriver.GetInsertCommand(table,refreshDataFromDatabase, dataColumns, InsertRowLockMode);
            DbDataAdapter adapter = GetDataAdapter(table, 
					string.Empty,
                    string.Empty, 
                    insertCommand,
                    string.Empty,
					dataColumns);
            DataTable dt = table.GetChanges(DataRowState.Added);
            if (dt != null) 
                adapter.Update(dt);
        }

        public void UpdateTableUpdate(DataTable table, string updateCommand, ICollection<DataColumn> dataColumns)
        {
            if (string.IsNullOrEmpty(updateCommand))
                updateCommand = sqlDriver.GetUpdateCommand(table, false,refreshDataFromDatabase, dataColumns, updateRowLockMode);
            DbDataAdapter adapter = GetDataAdapter(table,
                    string.Empty,
                    updateCommand,
                    string.Empty,
                    string.Empty,
                    dataColumns);
            DataTable dt = table.GetChanges(DataRowState.Modified);
            if (dt != null)
            adapter.Update(dt);
        }

        public void UpdateTableDelete(DataTable table, string deleteCommand, ICollection<DataColumn> dataColumns)
        {
            if (string.IsNullOrEmpty(deleteCommand))
                deleteCommand = sqlDriver.GetDeleteCommand(table, false, dataColumns, deleteRowLockMode);
            DbDataAdapter adapter = GetDataAdapter(table,
                    string.Empty,
                    string.Empty,
                    string.Empty,
                    deleteCommand,
                    dataColumns);
            DataTable dt = table.GetChanges(DataRowState.Deleted);
            if(dt!=null)
                adapter.Update(dt);
        }

        #endregion

        #region IDbRelationalSource Members

        public void UpdateTable(DataTable table, string updateCommand, 
            string insertCommand, string deleteCommand, 
            ICollection<DataColumn> dataColumns)
        {

            if (string.IsNullOrEmpty(insertCommand))
                insertCommand = sqlDriver.GetInsertCommand(table, refreshDataFromDatabase, dataColumns, InsertRowLockMode);
            
            if (string.IsNullOrEmpty(updateCommand))
                updateCommand = sqlDriver.GetUpdateCommand(table, false, refreshDataFromDatabase, dataColumns, UpdateRowLockMode);
            
            if (string.IsNullOrEmpty(deleteCommand))
                deleteCommand = sqlDriver.GetDeleteCommand(table, false, dataColumns, DeleteRowLockMode);

            DbDataAdapter adapter = GetDataAdapter(table,
                    sqlDriver.GetSelectCommand(table, dataColumns),
                    updateCommand,
                    insertCommand,
                    deleteCommand,
                    dataColumns);
            DataTable dt = table.GetChanges(DataRowState.Modified | DataRowState.Added);
            if (dt != null)
                adapter.Update(dt);

            //dt = table.GetChanges(DataRowState.Added);
            //if (dt != null)
            //    adapter.Update(dt);
        }

        #endregion




        #region IDbProviderSupplier Members

        IDbConnection Adoor.Data.IDbConnectionSupplier.DbConnection
        {
            get
            {
                IDbConnection connection = this.dbProviderFactory.CreateConnection();
                connection.ConnectionString = connectionStringBuilder.ConnectionString;
                return connection; 
                }
        }

        #endregion
    }

	public delegate void CommandEventHandler(object sender, CommandEventArgs e);

	public class CommandEventArgs : EventArgs
	{
		public CommandEventArgs(IDbCommand cmd)
		{
			this.cmd = cmd;
		}

		private IDbCommand cmd;
		public IDbCommand Cmd
		{
			get{ return cmd; }
		}
	}

	public delegate void QueryTraceEventHandler(object sender, QueryTraceEventArgs e);

	public class QueryTraceEventArgs : EventArgs
	{
		public QueryTraceEventArgs(string query)
		{
			this.query = query;
		}

		private string query;
		public string Query
		{
			get{ return query; }
		}
	}

	public delegate void DbRelationSourceRetryEventHandler(object sender, DbRelationSourceRetryEventArgs e);

	public class DbRelationSourceRetryEventArgs : EventArgs
	{
		public DbRelationSourceRetryEventArgs(bool retry, Exception ex)
		{
			this.retry = retry;
			this.ex = ex;
		}

		private bool retry;
		public bool Retry
		{
			get{ return retry; }
			set{ retry = value; }
		}

		private bool handled;
		public bool Handled
		{
			get{ return handled; }
			set{ handled = value; }
		}

		private Exception ex;
		public Exception Exception
		{
			get{ return ex;}
		}
	}
}
